/*
 * Last review by   : Yaser
 * Last review date : JUL-25-09 
 */
using System;
using System.Data;
using System.Data.SqlClient;
using ERP.Framework.Components.LoggingHelper.ErrorLog;
using ERP.Framework.Components.Utilities.TracingHelper;

namespace ERP.Framework.Components.DatabaseHelper.Providers
{
    /// <summary>
    /// An SQL database connector
    /// </summary>
    internal sealed class SQLDataBaseProvider : IDataBaseConnector
    {
        #region Constructors

        /// <summary>
        /// Initializes a new instance of the <see cref="SQLDataBaseProvider"/> class.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        public SQLDataBaseProvider(string connectionString)
        {
            ConnectionString = connectionString;
        }

        #endregion

        #region Members

        private const string TRACING_CATEGORY = "ERP.Framework.Components.DatabaseHelper.SQLDataBaseProvider";

        #endregion

        #region Properties

        /// <summary>
        /// the database connection string
        /// </summary>
        /// <value></value>
        public string ConnectionString
        {
            get;
            set;
        }

        /// <summary>
        /// the exceptions publishing method
        /// </summary>
        /// <value></value>
        public ErrorLogType ErrorLogType
        {
            get;
            set;
        }

        /// <summary>
        /// the exceptions log file path
        /// in case the log type was XML file
        /// </summary>
        /// <value></value>
        public string ErrorLogFilePath
        {
            get;
            set;
        }

        #endregion

        #region Methods

        /// <summary>
        /// Excecute update or insert statment
        /// </summary>
        /// <param name="type">stored proc/ text</param>
        /// <param name="command">command text</param>
        /// <param name="parameters">params collection</param>
        /// <returns>count of effected rows</returns>
        public int Save(CommandType type, string commandText, ParametersCollection parameters)
        {
            TraceUtil.Write(TRACING_CATEGORY, "Save : Start");
            int affectedRows = -1;

            //check for connection string
            if (ConnectionString.Equals(string.Empty))
            {
                ExceptionHandler.ExceptionPublisher.PublishException(new Exception(Resources.EmptyConnetionString));
                return affectedRows;
            }
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    using (SqlCommand command = new SqlCommand(commandText, connection))
                    {
                        //a switch used in case another types where added to the type Enum
                        switch (type)
                        {
                            case CommandType.StoredProcedure:
                                command.CommandType = System.Data.CommandType.StoredProcedure;
                                break;
                            case CommandType.InlineText:
                                command.CommandType = System.Data.CommandType.Text;
                                break;
                        }

                        //load the parameters
                        if (parameters != null)
                        {
                            foreach (Parameter par in parameters.Parameters)
                            {
                                command.Parameters.Add(par.Name, ParameterConvertor.GetSQLType(par.Type));
                                command.Parameters[command.Parameters.Count - 1].Value = par.Value == null ? DBNull.Value : par.Value;
                                command.Parameters[command.Parameters.Count - 1].Direction = ParameterConvertor.GetDirection(par.Direction);
                            }
                        }

                        SqlParameter ret = new SqlParameter();
                        ret.Direction = System.Data.ParameterDirection.ReturnValue;
                        ret.ParameterName = "@@ROWCOUNT";

                        connection.Open();
                        //save to database
                        affectedRows = command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                ExceptionHandler.ExceptionPublisher.PublishException(e);
            }
            TraceUtil.Write(TRACING_CATEGORY, "Save : End");
            return affectedRows;
        }

        /// <summary>
        /// Executes a select statement
        /// </summary>
        /// <param name="type">stored proc/ text</param>
        /// <param name="command">command text</param>
        /// <param name="parameters">params collection</param>
        /// <returns>table contains the query result</returns>
        public DataTable Read(CommandType type, string commandText, ParametersCollection parameters)
        {
            TraceUtil.Write(TRACING_CATEGORY, "Read : Start");
            //read data
            DataTable data = null;
            //check for connection string
            if (ConnectionString.Equals(string.Empty))
            {
                ExceptionHandler.ExceptionPublisher.PublishException(new Exception(Resources.EmptyConnetionString));
                return data;
            }
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    using (SqlCommand command = new SqlCommand(commandText, connection))
                    {
                        //a switch used in case another types where added to the type Enum
                        switch (type)
                        {
                            case CommandType.StoredProcedure:
                                command.CommandType = System.Data.CommandType.StoredProcedure;
                                break;
                            case CommandType.InlineText:
                                command.CommandType = System.Data.CommandType.Text;
                                break;
                        }

                        //load the parameters
                        if (parameters != null)
                            foreach (Parameter par in parameters.Parameters)
                            {
                                command.Parameters.Add(par.Name, ParameterConvertor.GetSQLType(par.Type));
                                command.Parameters[command.Parameters.Count - 1].Value = par.Value;
                                command.Parameters[command.Parameters.Count - 1].Direction = ParameterConvertor.GetDirection(par.Direction);
                            }
                        connection.Open();
                        //read from database
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                data = new DataTable();
                                //load table's schema
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    data.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
                                }
                                //Iterate on the rows
                                while (reader.Read())
                                {
                                    //add rows to table
                                    DataRow row = data.NewRow();
                                    foreach (DataColumn column in data.Columns)
                                        row[column.Ordinal] = reader[column.Ordinal];
                                    data.Rows.Add(row);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception e)
            {
                ExceptionHandler.ExceptionPublisher.PublishException(e);
            }
            TraceUtil.Write(TRACING_CATEGORY, "Read : End");
            //retun the read data
            return data;
        }

        /// <summary>
        /// Executes a select statement that returns one value [count, max ,...]
        /// </summary>
        /// <param name="type">stored proc/ text</param>
        /// <param name="command">command text</param>
        /// <param name="parameters">params collection</param>
        /// <returns>object contains the query result</returns>
        public object ReadScalar(CommandType type, string commandText, ParametersCollection parameters)
        {
            TraceUtil.Write(TRACING_CATEGORY, "ReadScalar : Start");
            //read data
            object data = null;

            //check for connection string
            if (ConnectionString.Equals(string.Empty))
            {
                ExceptionHandler.ExceptionPublisher.PublishException(new Exception(Resources.EmptyConnetionString));
                return data;
            }
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    using (SqlCommand command = new SqlCommand(commandText, connection))
                    {
                        //a switch used in case another types where added to the type Enum
                        switch (type)
                        {
                            case CommandType.StoredProcedure:
                                command.CommandType = System.Data.CommandType.StoredProcedure;
                                break;
                            case CommandType.InlineText:
                                command.CommandType = System.Data.CommandType.Text;
                                break;
                        }

                        //load the parameters
                        if (parameters != null)
                            foreach (Parameter par in parameters.Parameters)
                            {
                                command.Parameters.Add(par.Name, ParameterConvertor.GetSQLType(par.Type));
                                command.Parameters[command.Parameters.Count - 1].Value = par.Value;
                                command.Parameters[command.Parameters.Count - 1].Direction = ParameterConvertor.GetDirection(par.Direction);
                            }
                        connection.Open();
                        //read from database
                        data = command.ExecuteScalar();
                    }
                }
            }
            catch (Exception e)
            {
                ExceptionHandler.ExceptionPublisher.PublishException(e);
            }
            TraceUtil.Write(TRACING_CATEGORY, "ReadScalar : End");
            //retun the read data
            return data;
        }

        #endregion

    }
}
